<?php
namespace app\api\controller\system;

use think\App;
use think\facade\Db;
use app\common\controller\AdminController;
use EasyAdmin\annotation\ControllerAnnotation;
use EasyAdmin\annotation\NodeAnotation;
use Symfony\Component\VarDumper\Cloner\Data;
use app\admin\model\SystemUser;
use app\admin\model\SystemUserRole;
use app\admin\model\FigureUser;
use app\admin\model\FigurePartner;
use app\admin\model\FigureBalanceLog;
use app\admin\model\FigureMemberLog;
use app\admin\model\FigurePointLog;
use app\admin\model\FigureCardLog;
use app\admin\model\FigurePartnerLog;

/**
 * @ControllerAnnotation(title="用户")
 */
class User extends AdminController
{

    /**
     * 字段排序
     *
     * @var array
     */
    protected $sort = [
        'user_id' => 'desc'
    ];

    public function __construct(App $app)
    {
        parent::__construct($app);
        
        $this->model = new \app\admin\model\SystemUser();
    }

    /**
     * @NodeAnotation(title="列表")
     */
    public function index()
    {
        $request = $this->request;
        $user_id = $request->_UID;
        try {
            
            $nickname = input("nickname");
            $phone = input("phone");
            $u_id = input("user_id");
            
            $has_past = input("has_past");
            $past_data = input("past_data");
            
            $create_time = input("create_time");
            
            $page = input("page");
            $limit = input("limit");
            
            $where[] = [
                "tenant_id",
                "=",
                1
            ];
            
            if (! empty($u_id)) {
                $where[] = [
                    "agency_id",
                    "=",
                    $u_id
                ];
            }
            
            if (! empty(input('nickname'))) {
                $where[] = [
                    "nickname",
                    "LIKE",
                    "%" . input('nickname') . "%"
                ];
            }
            
            if (! empty(input('phone'))) {
                $where[] = [
                    "phone",
                    "LIKE",
                    "%" . input('phone') . "%"
                ];
            }
            
            if (! empty($has_past)) {
                if ($has_past == 1) {
                    $where[] = [
                        "has_past",
                        "=",
                        $has_past
                    ];
                } else {
                    $where[] = [
                        "has_past",
                        "=",
                        $has_past
                    ];
                    if (! empty($past_data)) {
                        $where[] = [
                            "past_data",
                            ">=",
                            strtotime($past_data[0])
                        ];
                        $where[] = [
                            "past_data",
                            "<=",
                            strtotime($past_data[1])
                        ];
                    }
                }
            }
            
            if (! empty(input('createTimeStart'))) {
                $where[] = [
                    "create_time",
                    ">=",
                    strtotime(input('createTimeStart'))
                ];
            }
            
            if (! empty(input('createTimeEnd'))) {
                $where[] = [
                    "create_time",
                    "<=",
                    strtotime(input('createTimeEnd'))
                ];
            }
            
            $list = $this->model->where($where)
                ->field("*,user_id userId,create_time createTime,update_time updateTime,IF(sex=1,'男','女') sexName,DATE_FORMAT(FROM_UNIXTIME(past_data),'%Y-%m-%d') past_data")
                ->limit(($page - 1) * $limit, $limit)
                ->order($this->sort)
                ->select();
            
            foreach ($list as $key => $value) {
                
                $roles = SystemUserRole::alias('a')->leftJoin('wjyk_system_role b', 'a.role_id=b.role_id')
                    ->where("a.user_id = {$value['user_id']}")
                    ->field("b.role_id roleId,b.role_name roleName,b.role_code roleCode,b.tenant_id tenantId")
                    ->select();
                
                $roleIds = [];
                foreach ($roles as $k => $v) {
                    array_push($roleIds, $v['roleId']);
                }
                $list[$key]['roleIds'] = $roleIds;
                
                $list[$key]['roles'] = $roles;
                
                $list[$key]['createTime'] = $value['create_time'];
            }
            
            $count = $this->model->where($where)->count();
            
            return msg([
                'list' => $list,
                'count' => $count
            ], 0, "");
        } catch (\Throwable $th) {
            return msg([], 1, "获取数据失败 : " . $th->getMessage());
        }
    }

    /**
     * @NodeAnotation(title="查询代理最新信息")
     */
    public function queryInfo()
    {
        $request = $this->request;
        $user_id = $request->_UID;
        try {
            
            $user = $this->model->where("user_id", $user_id)->find();
            
            return msg($user, 0, "");
        } catch (\Throwable $th) {
            return msg([], 1, "获取数据失败 : " . $th->getMessage());
        }
    }

    /**
     * 添加管理账号
     * @NodeAnotation(title="添加管理账号")
     */
    public function add()
    {
        $request = $this->request;
        $user_id = $request->_UID;
        if ($this->request->isPost()) {
            $post = $this->request->post();
            $rule = [];
            $this->validate($post, $rule);
            
            Db::startTrans();
            try {
                // require "../vendor/phpqrcode/phpqrcode.php";
                // if (! empty($post['past_data'])) {
                // $post['past_data'] = strtotime($post['past_data']);
                // }
                $data = [
                    "nickname" => $post['nickname'],
                    "password" => $post['password'],
                    "phone" => $post['phone'],
                    "sex" => $post['sex'],
                    "username" => $post['username'],
                    'tenant_id' => $request->_TENANTID
                ];
                
                if ($request->_TENANTID == 2) {
                    $agencyUser = SystemUser::where('user_id', $user_id)->find();
                    $data['agency_domain'] = empty($agencyUser['agency_domain']) ? '' : $agencyUser['agency_domain'];
                }
                
                $roleIds = $post['roleIds'];
                
                unset($post['roles']);
                unset($post['roleIds']);
                
                $user = SystemUser::where("user_id", $user_id)->find();
                
                if (! empty($post['user_id'])) {
                    
                    $data['update_time'] = date('Y-m-d H:i:s', time());
                    unset($post['password']);
                    unset($post['agencyId']);
                    
                    $save = SystemUser::where("user_id", $post['user_id'])->update($post);
                    
                    $id = $post['user_id'];
                } else {
                    $data['agency_id'] = $user_id;
                    
                    $data['create_time'] = time();
                    
                    $data['password'] = md5($post['password']);
                    $save = $this->model->save($data);
                    $id = $this->model->id;
                }
                
                if (! empty($roleIds)) {
                    $insert = [];
                    
                    SystemUserRole::where("user_id", $id)->delete();
                    
                    foreach ($roleIds as $key => $value) {
                        $insert[] = array(
                            'role_id' => $value,
                            'user_id' => $id,
                            'tenant_id' => $data['tenant_id'],
                            'create_time' => time()
                        );
                    }
                    
                    if (! empty($insert)) {
                        SystemUserRole::insertAll($insert);
                    }
                }
                
                Db::commit();
                return empty($post['user_id']) ? msg([], 0, "新增成功") : msg([], 0, "修改成功");
            } catch (\Exception $e) {
                Db::rollback();
                return msg([], 1, "保存失败 " . $e->getMessage() . " line:" . $e->getLine());
            }
        }
    }

    /**
     * @NodeAnotation(title="删除")
     */
    public function del($id)
    {
        $row = $this->model->whereIn('user_id', $id)->select();
        if ($row->isEmpty()) {
            return msg([], 1, "数据不存在");
        }
        try {
            $save = Db::table('wjyk_system_user')->whereIn('user_id', $id)->delete();
        } catch (\Exception $e) {
            return msg([], 1, "删除异常" . $e->getMessage());
        }
        return $save ? msg([], 0, "删除成功") : msg([], 1, "删除失败");
    }

    /**
     * @NodeAnotation(title="变更用户状态")
     */
    public function status()
    {
        $request = $this->request;
        
        try {
            $user_id = input("user_id");
            $status = input("status");
            
            SystemUser::where("user_id", $user_id)->update([
                "status" => $status
            ]);
            
            return msg([], 0, "变更状态成功");
        } catch (\Throwable $th) {
            return msg([], 1, "获取数据失败 : " . $th->getMessage());
        }
    }

    /**
     * 添加更新商户
     * @NodeAnotation(title="添加更新商户")
     */
    public function reloadQrcode()
    {
        if ($this->request->isPost()) {
            $post = $this->request->post();
            $rule = [];
            $this->validate($post, $rule);
            try {
                require "../vendor/phpqrcode/phpqrcode.php";
                $user_id = $post['user_id'];
                $url = getDyQrcode($user_id, "");
                User::where("user_id", $user_id)->update([
                    "qrcode" => $url
                ]);
                return msg([], 0, "生成成功");
            } catch (\Exception $e) {
                return msg([], 1, "更新失败 " . $e->getMessage());
            }
        }
    }

    /**
     * 添加前验证
     * @NodeAnotation(title="")
     */
    public function existence()
    {
        $request = $this->request;
        try {
            $field = input("field");
            $value = input("value");
            
            $count = $this->model->where($field, $value)->count();
            if ($count > 0) {
                return msg([], 0, "账号已存在");
            }
            
            return msg([], 1, "可以新增");
        } catch (\Throwable $th) {
            return msg([], 1, "获取数据失败 : " . $th->getMessage());
        }
    }

    /**
     * 重置密码
     * @NodeAnotation(title="")
     */
    public function password()
    {
        $request = $this->request;
        try {
            $password = input("password");
            $userId = input("userId");
            $user = $this->model->where("user_id", $userId)->find();
            if (empty($user)) {
                return msg([], 1, "账号不存在");
            }
            $user->password = md5($password);
            $user->save();
            return msg([], 0, "重置成功");
        } catch (\Throwable $th) {
            return msg([], 1, "重置失败 : " . $th->getMessage());
        }
    }

    /**
     * 获取商户信息
     * @NodeAnotation(title="")
     */
    public function getUser()
    {
        $request = $this->request;
        $user_id = $this->request->_UID;
        
        try {
            $id = input("id");
            $user = $this->model->where("user_id", $id)
                ->field("*,create_time createTime")
                ->find();
            
            return msg($user, 0, "查询账号成功");
        } catch (\Throwable $th) {
            return msg([], 1, "获取数据失败 : " . $th->getMessage());
        }
    }

    /**
     * 获取管理员首页数据
     * @NodeAnotation(title="获取管理员首页数据")
     */
    public function getAdminProfileData()
    {
        $user_count = FigureUser::count();
        
        $user['user_count'] = $user_count;
        
        return msg($user, 0, "查询成功");
    }

    /**
     * 获取管理员首页数据
     * @NodeAnotation(title="获取管理员首页数据")
     */
    public function getAdminSaleData()
    {
        $rechargeMoney = FigurePointLog::where("pay_status = 2")->sum('money');
        $orderMoney = FigureMemberLog::where("pay_status = 2")->sum('money');
        $cardMoney = FigureCardLog::where("pay_status = 2")->sum('money');
        $partnerMoney = FigureMemberLog::where("pay_status = 2")->sum('money');
        $saleMoney = round($rechargeMoney + $orderMoney + $cardMoney + $partnerMoney, 2);
        
        $start_time = strtotime('this week monday 00:00:00');
        $end_time = strtotime('this week sunday 23:59:59');
        
        $rechargeThisWeekMoney = FigurePointLog::where("pay_status = 2 AND create_time >= {$start_time} AND create_time <= {$end_time}")->sum('money');
        $orderThisWeekMoney = FigureMemberLog::where("pay_status = 2 AND create_time >= {$start_time} AND create_time <= {$end_time}")->sum('money');
        $cardThisWeekMoney = FigureCardLog::where("pay_status = 2 AND create_time >= {$start_time} AND create_time <= {$end_time}")->sum('money');
        $partnerThisWeekMoney = FigurePartnerLog::where("pay_status = 2 AND create_time >= {$start_time} AND create_time <= {$end_time}")->sum('money');
        $saleThisWeekMoney = round($rechargeThisWeekMoney + $orderThisWeekMoney + $cardThisWeekMoney + $partnerThisWeekMoney, 2);
        
        $start_time = strtotime("monday last week 00:00:00");
        $end_time = strtotime("sunday last week 23:59:59");
        
        $rechargeLastWeekMoney = FigurePointLog::where("pay_status = 2 AND create_time >= {$start_time} AND create_time <= {$end_time}")->sum('money');
        $orderLastWeekMoney = FigureMemberLog::where("pay_status = 2  AND create_time >= {$start_time} AND create_time <= {$end_time}")->sum('money');
        $cardLastWeekMoney = FigureCardLog::where("pay_status = 2 AND create_time >= {$start_time} AND create_time <= {$end_time}")->sum('money');
        $partnerLastWeekMoney = FigurePartnerLog::where("pay_status = 2 AND create_time >= {$start_time} AND create_time <= {$end_time}")->sum('money');
        
        $saleLastWeekMoney = round($rechargeLastWeekMoney + $orderLastWeekMoney + $cardLastWeekMoney + $partnerLastWeekMoney, 2);
        
        $sale_week_rate = $this->calRate($saleThisWeekMoney, $saleLastWeekMoney);
        
        $date = date('Y-m-d', time());
        
        $rechargeTodayMoney = FigurePointLog::where("pay_status = 2 AND DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m-%d') = '{$date}'  ")->sum('money');
        $orderTodayMoney = FigureMemberLog::where("pay_status = 2 AND DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m-%d') = '{$date}' ")->sum('money');
        $cardTodayMoney = FigureCardLog::where("pay_status = 2 AND DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m-%d') = '{$date}'")->sum('money');
        $partnerTodayMoney = FigurePartnerLog::where("pay_status = 2 AND DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m-%d') = '{$date}'")->sum('money');
        $saleTodayMoney = round($rechargeTodayMoney + $orderTodayMoney + $cardTodayMoney + $partnerTodayMoney, 2);
        
        $lastday_start_time = strtotime("-1 day 00:00:00");
        $lastday_end_time = strtotime("-1 day 23:59:59");
        
        $rechargeLastDayMoney = FigurePointLog::where("pay_status = 2 AND create_time >= {$lastday_start_time} AND create_time <= {$lastday_end_time}")->sum('money');
        $orderLastDayMoney = FigureMemberLog::where("pay_status = 2 AND create_time >= {$lastday_start_time} AND create_time <= {$lastday_end_time}")->sum('money');
        $saleLastDayMoney = round($rechargeLastDayMoney + $orderLastDayMoney, 2);
        
        $sale_day_rate = $this->calRate($saleTodayMoney, $saleLastDayMoney);
        
        $user_count = FigureUser::count();
        $day_user_count = FigureUser::where([
            
            [
                "create_time",
                ">",
                strtotime(date('Y-m-d', time()))
            ],
            [
                "create_time",
                "<=",
                time()
            ]
        ])->count();
        
        $lastday_user_count = FigureUser::where("create_time >= {$lastday_start_time} AND create_time <= {$lastday_end_time}")->count();
        $user_count_rate = $this->calRate($day_user_count, $lastday_user_count);
        
        $month = date('Y-m', time());
        $month_user_count = FigureUser::where(" DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m') = '{$month}'")->count();
        
        $partner_count = FigurePartner::count();
        $day_partner_count = FigurePartner::where([
            [
                "create_time",
                ">",
                strtotime(date('Y-m-d', time()))
            ],
            [
                "create_time",
                "<=",
                time()
            ]
        ])->count();
        
        $lastday_partner_count = FigurePartner::where("create_time >= {$lastday_start_time} AND create_time <= {$lastday_end_time}")->count();
        $partner_count_rate = $this->calRate($day_partner_count, $lastday_partner_count);
        $month_partner_count = FigurePartner::where(" DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m') = '{$month}'")->count();
        
        $recharge_count = FigurePointLog::where("pay_status = 2")->count();
        $day_recharge_count = FigurePointLog::where([
            [
                "pay_status",
                "=",
                2
            ],
            [
                "create_time",
                ">",
                strtotime(date('Y-m-d', time()))
            ],
            [
                "create_time",
                "<=",
                time()
            ]
        ])->count();
        $lastday_recharge_count = FigurePointLog::where("pay_status = 2 AND create_time >= {$lastday_start_time} AND create_time <= {$lastday_end_time}")->count();
        $recharge_count_rate = $this->calRate($day_recharge_count, $lastday_recharge_count);
        $month_recharge_count = FigurePointLog::where("pay_status = 2 AND DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m') = '{$month}'")->count();
        
        return msg([
            "sale_money" => $saleMoney,
            "day_sale_money" => $saleTodayMoney,
            "sale_week_rate" => $sale_week_rate,
            "sale_day_rate" => $sale_day_rate,
            "u_count" => $user_count,
            "day_u_count" => $day_user_count,
            "lastday_u_count" => $lastday_user_count,
            "user_count_rate" => $user_count_rate,
            "month_u_count" => $month_user_count,
            
            "partner_count" => $partner_count,
            "day_partner_count" => $day_partner_count,
            "lastday_partner_count" => $lastday_partner_count,
            "partner_count_rate" => $partner_count_rate,
            "month_partner_count" => $month_partner_count,
            
            "recharge_count" => $recharge_count,
            "day_recharge_count" => $day_recharge_count,
            "lastday_recharge_count" => $lastday_recharge_count,
            "recharge_count_rate" => $recharge_count_rate,
            "month_recharge_count" => $month_recharge_count
        ], 0, "查询成功");
    }

    public function getAdminChartData()
    {
        $request = $this->request;
        $user_id = $this->request->_UID;
        
        $fieldValue = "";
        
        $type = input('type');
        if ($type == 0) { // 今天
            
            $start_time = strtotime(date('Y-m-d', time()));
            $end_time = time();
            
            $dateResult = $this->getChartHourSql();
            
            $fieldValue = "*,DATE_FORMAT(FROM_UNIXTIME(create_time),'%H') day_time";
        } elseif ($type == 1) { // 本周
            
            $start_time = strtotime("-7 day", time());
            $end_time = time();
            
            $dateResult = $this->getChartDateSql($start_time, $end_time);
            
            $fieldValue = "*,DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m-%d') day_time";
        } elseif ($type == 2) { // 本月
            
            $start_time = strtotime("-30 day", time());
            $end_time = time();
            
            $dateResult = $this->getChartDateSql($start_time, $end_time);
            
            $fieldValue = "*,DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m-%d') day_time";
        } elseif ($type == 3) { // 本年
            $start_time = strtotime("-12 month", time());
            $end_time = time();
            
            $dateResult = $this->getChartMonthSql($start_time, $end_time);
            
            $fieldValue = "*,DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m') day_time";
        } else {
            
            $start_time = strtotime(input('start_time'));
            $end_time = strtotime(input('end_time'));
            
            $dateResult = $this->getChartDateSql($start_time, $end_time);
            
            $fieldValue = "*,DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m-%d') day_time";
        }
        
        $where = "  create_time >= {$start_time} AND create_time <= {$end_time}";
        
        $partnerSubQuery = FigurePartner::where($where)->field($fieldValue)->buildSql();
        $partnerSecondQuery = Db::table($partnerSubQuery . ' a')->field("a.day_time,count(1) count")
            ->group('a.day_time')
            ->buildSql();
        
        $partner_data = $this->dateShenjiJoin($partnerSecondQuery, $dateResult['sql'], "w.date month,IFNULL(a.count,0) count");
        
        $partner_data_count = array_column($partner_data, 'count');
        
        $memberSubQuery = FigureMemberLog::where($where . " AND pay_status = 2 ")->field($fieldValue)->buildSql();
        $memberSecondQuery = Db::table($memberSubQuery . ' a')->field("a.day_time,count(1) count,sum(money) num")
            ->group('a.day_time')
            ->buildSql();
        
        $member_data = $this->dateShenjiJoin($memberSecondQuery, $dateResult['sql'], "w.date month,IFNULL(a.count,0) count,IFNULL(a.num,0) value");
        
        $member_data_count = array_column($member_data, 'count');
        $member_data_value = array_column($member_data, 'value');
        
        $pointSubQuery = FigurePointLog::where($where . " AND pay_status = 2")->field($fieldValue)->buildSql();
        $pointSecondQuery = Db::table($pointSubQuery . ' a')->field("a.day_time,count(1) count,sum(point) num")
            ->group('a.day_time')
            ->buildSql();
        
        $point_cz = $this->dateShenjiJoin($pointSecondQuery, $dateResult['sql'], "w.date month,IFNULL(a.count,0) count,IFNULL(a.num,0) value");
        
        $point_cz_count = array_column($point_cz, 'count');
        $point_cz_value = array_column($point_cz, 'value');
        
        $point_ranking = FigureUser::alias('a')->leftJoin('wjyk_figure_point_log o', "a.id =o.uid AND  o.create_time >= {$start_time} AND o.create_time <= {$end_time}")
            ->where("o.pay_status = 2")
            ->field("*,IFNULL(count(o.uid),0) count,IFNULL(sum(o.point),0) num,a.nickname,a.telphone")
            ->group("a.id")
            ->order("count", "desc")
            ->select();
        
        $balanceSubQuery = FigureBalanceLog::where($where . " AND type = 2")->field($fieldValue)->buildSql();
        $balanceSecondQuery = Db::table($balanceSubQuery . ' a')->field("a.day_time,count(1) count,sum(money) num")
            ->group('a.day_time')
            ->buildSql();
        
        $balance_data = $this->dateShenjiJoin($balanceSecondQuery, $dateResult['sql'], "w.date month,IFNULL(a.count,0) count,IFNULL(a.num,0) value");
        
        $balance_data_count = array_column($balance_data, 'count');
        $balance_data_value = array_column($balance_data, 'value');
        
        $balance_ranking = FigureBalanceLog::alias('a')->where("a.type = 2 AND a.create_time >= {$start_time} AND a.create_time <= {$end_time}")
            ->field("*,IFNULL(sum(a.money),0) num")
            ->group("a.way")
            ->order("num", "desc")
            ->select();
        
        return msg([
            "date" => $dateResult['date'],
            "partner" => array(
                'count' => $partner_data_count
            ),
            "member" => array(
                'value' => $member_data_value,
                'count' => $member_data_count
            ),
            "point" => array(
                'value' => $point_cz_value,
                'count' => $point_cz_count,
                'ranking' => $point_ranking
            ),
            "balance" => array(
                'value' => $balance_data_value,
                'count' => $balance_data_count,
                'ranking' => $balance_ranking
            )
        ], 0, "查询成功");
    }

    /**
     * 计算环比增长率
     *
     * @param
     *            today 本月值
     * @param
     *            yes 上个月值
     */
    function calRate($today, $yes)
    {
        $rate = $yes <= 0 ? ($today - $yes) * (100 / 100) : ($today - $yes) / $yes * (100 / 100);
        
        $rate = round($rate, 2);
        return $rate;
    }

    /**
     * 日期连接查询
     *
     * @param sql $subQuery
     *            统计sql
     * @param sql $sql
     *            日期sql
     */
    private function dateShenjiJoin($subQuery, $sql, $fields)
    {
        $result = Db::table($subQuery . ' a')->rightJoin([
            $sql => 'w'
        ], "a.day_time=w.date")
            ->order("w.date", "asc")
            ->field($fields)
            ->select()
            ->toArray();
        return $result;
    }

    /**
     * 获取图表日期sql
     */
    function getChartDateSql($start_time, $end_time)
    {
        $dateList = array();
        array_push($dateList, date('Y-m-d', $start_time));
        $sql = "(
        SELECT
        '" . date('Y-m-d', $start_time) . "' AS date
        FROM DUAL ";
        
        for ($i = $start_time; $i < $end_time;) {
            
            $date = date('Y-m-d', strtotime("+1 day", $i));
            
            if (strtotime($date) < $end_time) {
                array_push($dateList, $date);
            }
            
            $sql .= "UNION ALL
        SELECT
            '" . $date . "' AS date
        FROM DUAL ";
            $i = strtotime("+1 day", $i);
        }
        $sql .= ")";
        return [
            "sql" => $sql,
            "date" => $dateList
        ];
    }

    /**
     * 获取图表日期sql
     */
    function getChartMonthSql($start_time, $end_time)
    {
        $dateList = array();
        array_push($dateList, date('Y-m', $start_time));
        $sql = "(
        SELECT
        '" . date('Y-m', $start_time) . "' AS date
        FROM DUAL ";
        
        for ($i = $start_time; $i < $end_time;) {
            
            $date = date('Y-m', strtotime("+1 month", $i));
            
            if (strtotime($date) < $end_time) {
                array_push($dateList, $date);
            }
            
            $sql .= "UNION ALL
        SELECT
            '" . $date . "' AS date
        FROM DUAL ";
            $i = strtotime("+1 month", $i);
        }
        $sql .= ")";
        return [
            "sql" => $sql,
            "date" => $dateList
        ];
    }

    function getChartHourSql()
    {
        $hour_arr = [
            '00',
            '01',
            '02',
            '03',
            '04',
            '05',
            '06',
            '07',
            '08',
            '09',
            '10',
            '11',
            '12',
            '13',
            '14',
            '15',
            '16',
            '17',
            '18',
            '19',
            '20',
            '21',
            '22',
            '23',
            '24'
        ];
        
        $sql = "";
        foreach ($hour_arr as $k => $v) {
            
            if ($k == 0) {
                $sql = "( SELECT '" . $v . "' AS date FROM DUAL ";
                continue;
            }
            
            $sql .= "UNION ALL SELECT '" . $v . "' AS date FROM DUAL ";
        }
        $sql .= ")";
        
        return [
            "sql" => $sql,
            "date" => $hour_arr
        ];
    }
}